﻿using System;
using System.Data.SqlClient;
using System.Data;
using System.Collections;
using System.Collections.Generic;

/*********************************************
 * Handle database retirval and update
 * 
 * Methods:
 * 
 * 
 * 
 * @Author: 
 * @Date: 
 * ********************************************/

namespace Shop_Inventory_Management.Database
{
    class DBManager
    {
        private static DBManager DBManagerInstance;
        private HQDBConnectionManager HQDBConn = HQDBConnectionManager.getInstance();
        private DataSet HQDsTableList = new DataSet();
        private SqlConnection HQSQLConn = new SqlConnection();
        private ShopDBConnectionManager ShopDBConn = ShopDBConnectionManager.getInstance();
        private DataSet ShopDsTableList = new DataSet();
        private SqlConnection ShopSQLConn = new SqlConnection();

        /**************************** HQ Adapter **************************/
        private SqlDataAdapter HQManufacturerAdapter;
        private SqlDataAdapter HQProductAdapter;
        private SqlDataAdapter HQStockAdapter;
        private SqlDataAdapter HQDiscountAdapter;

        /************************** Shop Adapter **************************/
        private SqlDataAdapter ShopManufacturerAdapter;
        private SqlDataAdapter ShopProductAdapter;
        private SqlDataAdapter ShopStockAdapter;
        private SqlDataAdapter ShopStockDiscountAdapter;
        private SqlDataAdapter ShopDiscountAdapter;

        private SqlDataAdapter HQReportAdapter;
        private SqlDataAdapter ShopReportAdapter;
        private SqlCommandBuilder HQReportcommandBuilder;
        private SqlCommandBuilder ShopReportCommandBuilder;

        /**************************** HQ CommandBuilder **************************/
        private SqlCommandBuilder HQManufacturerCommandBuilder;
        private SqlCommandBuilder HQProductCommandBuilder;
        private SqlCommandBuilder HQStockCommandBuilder;
        private SqlCommandBuilder HQDiscountCommandBuilder;

        /************************** Shop CommandBuilder **************************/
        private SqlCommandBuilder ShopManufacturerCommandBuilder;
        private SqlCommandBuilder ShopProductCommandBuilder;
        private SqlCommandBuilder ShopStockCommandBuilder;
        private SqlCommandBuilder ShopStockDiscountCommandBuilder;
        private SqlCommandBuilder ShopDiscountCommandBuilder;

        public static DBManager getInstance()
        {
            if (DBManagerInstance == null)
            {
                DBManagerInstance = new DBManager();
            }
            return DBManagerInstance;
        }

        private DBManager()
        {
            HQSQLConn.ConnectionString = HQDBConn.getConnectionString();
            ShopSQLConn.ConnectionString = ShopDBConn.getConnectionString();

            try
            {
                HQDBInitialization();
                ShopDBInitialization();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                CheckAndCloseHQSQLConnection();
                CheckAndCloseShopSQLConnection();
            }
        }

        #region HQDB Initialization

        private void HQDBInitialization()
        {
            HQSQLConn.Open();
            InitializeHQSelectCommand();
            InitializeHQAdapterCommands();
            HQSQLConn.Close();
        }

        private void InitializeHQSelectCommand()
        {
            HQManufacturerAdapter = new SqlDataAdapter(Constant.MANUFACTURER_TABLE_SELECT_COMMAND, HQSQLConn);
            HQProductAdapter = new SqlDataAdapter(Constant.PRODUCT_TABLE_SELECT_COMMAND, HQSQLConn);
            HQStockAdapter = new SqlDataAdapter(Constant.STOCK_TABLE_SELECT_COMMAND, HQSQLConn);
            HQDiscountAdapter = new SqlDataAdapter(Constant.DISCOUNT_TABLE_SELECT_COMMAND, HQSQLConn);
            HQReportAdapter = new SqlDataAdapter(Constant.REPORT_TABLE_SELECT_COMMAND, HQSQLConn);
        }

        private void InitializeHQAdapterCommands()
        {
            HQManufacturerCommandBuilder = new SqlCommandBuilder(HQManufacturerAdapter);
            HQProductCommandBuilder = new SqlCommandBuilder(HQProductAdapter);
            HQStockCommandBuilder = new SqlCommandBuilder(HQStockAdapter);
            HQDiscountCommandBuilder = new SqlCommandBuilder(HQDiscountAdapter);
            HQReportcommandBuilder = new SqlCommandBuilder(HQReportAdapter);
        }

        private void FillHQDataSet()
        {
            HQManufacturerAdapter.Fill(HQDsTableList, Constant.MANUFACTURER_TABLE);
            HQProductAdapter.Fill(HQDsTableList, Constant.PRODUCT_TABLE);
            HQStockAdapter.Fill(HQDsTableList, Constant.STOCK_TABLE);
            HQDiscountAdapter.Fill(HQDsTableList, Constant.DISCOUNT_TABLE);
            HQReportAdapter.Fill(HQDsTableList, Constant.REPORT_TABLE);
        }

        private void CheckAndCloseHQSQLConnection()
        {
            if (HQSQLConn.State == ConnectionState.Open)
            {
                HQSQLConn.Close();
            }
        }

        #endregion

        #region ShopDB Initialization

        private void ShopDBInitialization()
        {
            ShopSQLConn.Open();
            InitializeShopSelectCommander();
            InitializeShopAdapterCommands();
            ShopSQLConn.Close();
        }

        private void InitializeShopSelectCommander()
        {
            ShopManufacturerAdapter = new SqlDataAdapter(Constant.MANUFACTURER_TABLE_SELECT_COMMAND, ShopSQLConn);
            ShopProductAdapter = new SqlDataAdapter(Constant.PRODUCT_TABLE_SELECT_COMMAND, ShopSQLConn);
            ShopStockAdapter = new SqlDataAdapter(Constant.STOCK_TABLE_SELECT_COMMAND, ShopSQLConn);
            ShopDiscountAdapter = new SqlDataAdapter(Constant.DISCOUNT_TABLE_SELECT_COMMAND, ShopSQLConn);
            ShopStockDiscountAdapter = new SqlDataAdapter(Constant.STOCK_DISCOUNT_SELECT_COMMAND, ShopSQLConn);
            ShopReportAdapter = new SqlDataAdapter(Constant.TOTAL_TRANSACTION_TABLE_SELECT_COMMAND, ShopSQLConn);
        }

        private void InitializeShopAdapterCommands()
        {
            ShopManufacturerCommandBuilder = new SqlCommandBuilder(ShopManufacturerAdapter);
            ShopProductCommandBuilder = new SqlCommandBuilder(ShopProductAdapter);
            ShopStockCommandBuilder = new SqlCommandBuilder(ShopStockAdapter);
            ShopStockDiscountCommandBuilder = new SqlCommandBuilder(ShopStockDiscountAdapter);
            ShopDiscountCommandBuilder = new SqlCommandBuilder(ShopDiscountAdapter);
            ShopReportCommandBuilder = new SqlCommandBuilder(ShopReportAdapter);
        }

        private void FillShopDataSet()
        {
            ShopManufacturerAdapter.Fill(ShopDsTableList, Constant.MANUFACTURER_TABLE);
            ShopProductAdapter.Fill(ShopDsTableList, Constant.PRODUCT_TABLE);
            ShopStockAdapter.Fill(ShopDsTableList, Constant.STOCK_TABLE);
            ShopDiscountAdapter.Fill(ShopDsTableList, Constant.DISCOUNT_TABLE);
            ShopReportAdapter.Fill(ShopDsTableList, Constant.REPORT_TABLE);
        }

        private void CheckAndCloseShopSQLConnection()
        {
            if (ShopSQLConn.State == ConnectionState.Open)
            {
                ShopSQLConn.Close();
            }
        }

        #endregion

        #region API For Synchronization

        public void RefleshDataSet()
        {
            ShopSQLConn.Open();
            HQDsTableList.Clear();
            ShopDsTableList.Clear();
            FillHQDataSet();
            FillShopDataSet();
            ShopSQLConn.Close();
        }

        public DataSet GetHQDsTableList()
        {
            return HQDsTableList;
        }

        public DataSet GetShopDsTableList()
        {
            return ShopDsTableList;
        }

        public void UpdateDatabase(string tableName)
        {
            try
            {
                ShopSQLConn.Open();
                switch (tableName)
                {
                    case Constant.MANUFACTURER_TABLE:
                        ShopManufacturerAdapter.Update(ShopDsTableList, Constant.MANUFACTURER_TABLE);
                        break;

                    case Constant.PRODUCT_TABLE:
                        ShopProductAdapter.Update(ShopDsTableList, Constant.PRODUCT_TABLE);
                        break;

                    case Constant.STOCK_TABLE:
                        ShopStockAdapter.Update(ShopDsTableList, Constant.STOCK_TABLE);
                        break;

                    case Constant.DISCOUNT_TABLE:
                        ShopDiscountAdapter.Update(ShopDsTableList, Constant.DISCOUNT_TABLE);
                        break;
                }
                ShopSQLConn.Close();
            }
            catch
            {
                ShopSQLConn.Close();
                throw;
            }
        }

        public void UpdateShopStockPrice()
        {
            ShopSQLConn.Open();
            ShopStockDiscountAdapter.Update(ShopDsTableList, Constant.STOCK_TABLE);
            ShopSQLConn.Close();
        }

        public void RefleshShopStock()
        {
            ShopDsTableList.Clear();
            ShopStockDiscountAdapter.Fill(ShopDsTableList, Constant.STOCK_TABLE);
        }

        #endregion
    }
}
